package com.xiaowei.gym.gymjsp.ware.dao.impl;

import com.mysql.cj.util.StringUtils;
import com.xiaowei.gym.gymjsp.ware.dao.MemberDao;
import com.xiaowei.gym.gymjsp.ware.entity.MemberEntity;
import com.xiaowei.gym.gymjsp.ware.entity.PersonnelEntity;
import com.xiaowei.gym.gymjsp.ware.entity.ReservationEntity;
import com.xiaowei.gym.gymjsp.ware.util.JdbcUtil;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class MemberDaoImpl implements MemberDao {
    @Override
    public int getCount() {
        String  sql = "SELECT count(1) FROM gym_member";
        int i = 0;
        try {
            i = JdbcUtil.executeQuery(sql, rs -> {
                int counts = 0;
                while (rs.next()) {
                    counts = rs.getInt("count(1)");
                }
                return counts;
            }, null);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return i;
    }

    @Override
    public List<MemberEntity> selectMemberList(String name, int currentPageNo, Integer pageSize) {
        List<MemberEntity> memberEntities = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" select * from gym_member ");
        List<Object> list = new ArrayList<>();
        if (!StringUtils.isNullOrEmpty(name)){
            sql.append("where name like ?");
            list.add("%"+name+"%");
        }
        //在mysql数据库中，分页使用 limit startIndex，pageSize ; 总数
        sql.append("order by create_date DESC limit ?,?");
        currentPageNo = (currentPageNo - 1) * pageSize;
        list.add(currentPageNo);
        list.add(pageSize);
        Object[] params = list.toArray();
        System.out.println("sql ----> " + sql.toString());
        try {
            List<MemberEntity> memberEntityList = new ArrayList<>();
            memberEntities = JdbcUtil.executeQuery(sql.toString(), rs -> {
                while (rs.next()) {
                    MemberEntity memberEntity = new MemberEntity();
                    memberEntity.setId(rs.getInt("id"));
                    memberEntity.setName(rs.getString("name"));
                    memberEntity.setGender(rs.getString("gender"));
                    memberEntity.setAge(rs.getInt("age"));
                    memberEntity.setPhone(rs.getString("phone"));
                    memberEntity.setAddress(rs.getString("address"));
                    memberEntity.setCard_number(rs.getString("card_number"));
                    memberEntity.setCreate_date(rs.getTimestamp("create_date"));
                    memberEntityList.add(memberEntity);
                }
                return memberEntityList;
            }, params);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return memberEntities;
    }

    @Override
    public int createMember(MemberEntity memberEntity) {
        String sql = "INSERT INTO gym_member (name, gender, age, phone, address, card_number, create_date) \n" +
                "VALUES (?, ?, ?, ?, ?, ?, ?)";
        Object[] params = {memberEntity.getName(),memberEntity.getGender(),memberEntity.getAge(),
                        memberEntity.getPhone(),memberEntity.getAddress(),memberEntity.getCard_number(),memberEntity.getCreate_date()};
        int i = 0;
        try {
            i = JdbcUtil.executeUpdate(sql, params);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return i;
    }

    @Override
    public int[] delMemberByIds(Integer[] delId) {

        String sql = "DELETE gm, gr FROM gym_member gm JOIN gym_reservation gr ON gm.id = gr.member_id WHERE gm.id = ?";
        Object[] params = delId;
        System.out.println(params);
        int[] i = null;
        try {
            i = JdbcUtil.batchDelete(sql, params);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return i;
    }

    @Override
    public List<MemberEntity> getMemberAll() {

        String sql = "SELECT id,name FROM gym_member";
        List<MemberEntity> memberEntities = null;
        try {
            memberEntities = JdbcUtil.executeQuery(sql, rs -> {
                List<MemberEntity> memberEntityList = new ArrayList<>();
                while (rs.next()) {
                    MemberEntity memberEntity = new MemberEntity();
                    memberEntity.setId(rs.getInt("id"));
                    memberEntity.setName(rs.getString("name"));
                    memberEntityList.add(memberEntity);
                }
                return memberEntityList;
            }, null);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return memberEntities;
    }

    @Override
    public MemberEntity getById(Integer valueOf) {
        String sql = "SELECT * FROM gym_member WHERE id = ?";
        MemberEntity memberEntity = null;
        try {
            memberEntity = JdbcUtil.executeQuery(sql, new JdbcUtil.ResultSetHandler<MemberEntity>() {
                @Override
                public MemberEntity handle(ResultSet rs) throws SQLException {
                    MemberEntity memberEntity = new MemberEntity();
                    if (rs.next()) {
                        memberEntity.setId(rs.getInt("id"));
                        memberEntity.setName(rs.getString("name"));
                        memberEntity.setAddress(rs.getString("address"));
                        memberEntity.setPhone(rs.getString("phone"));
                        memberEntity.setGender(rs.getString("gender"));
                        memberEntity.setAge(rs.getInt("age"));
                        memberEntity.setCard_number(rs.getString("card_number"));
                        memberEntity.setCreate_date(rs.getTimestamp("create_date"));
                    }
                    return memberEntity;
                }
            }, valueOf);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return memberEntity;
    }

    @Override
    public int modify(MemberEntity memberEntity) {
        String sql = "UPDATE gym_member SET name = ?,gender = ?,age = ?,phone = ?,address =? WHERE id = ?";
        Object[] params = {memberEntity.getName(),memberEntity.getGender(),memberEntity.getAge(),memberEntity.getPhone(),memberEntity.getAddress(),memberEntity.getId()};
        int i = 0;
        try {
            i = JdbcUtil.executeUpdate(sql, params);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return i;
    }
}
